var dbcon = require("../bookstore_db");

var connection = dbcon.getconnection();

connection.connect();

var express = require('express');

var router = express.Router();

//注意这个下面是 select * from book 是SQL查询语句，通过 server.js 中的 app.use("/api/books", bookAPI); 传到web上
router.get("/", (req, res) => {
    let category = req.query.category
    let sql = "select b.*,c.name from book b left join category c on b.category_ID=c.category_ID"
    if (category && category.trim() !== '') {
        sql += ` where b.category_ID=${category} `
    }
    connection.query(sql,
        (err, records, fields) => {
            if (err) {
                console.error("Error while retrieve the data");
            } else {
                res.send(records);
            }
        })
})
router.get("/category", (req, res) => {
    connection.query("select * from category ",
        (err, records, fields) => {
            if (err) {
                console.error("Error while retrieve the data");
            } else {
                res.send(records);
            }
        })
})

router.get("/:id", (req, res) => {
    connection.query("select * from book where book_ID=" + req.params.id, (err, records, fields) => {
        if (err) {
            console.error("Error while retrieve the data");
        } else {
            res.send(records);
        }
    })
})

//post 方法
router.post("/", (req, res) => {
    //一下是数据库中 某张表的每一列
    var book_ID = req.body.book_ID;
    var title = req.body.title;
    var author = req.body.author;
    var price = req.body.price;
    var description = req.body.description;
    var category_ID = req.body.category_ID;
    //通过SQL语句像每一行中插入各个分项
    connection.query("INSERT INTO book VALUES(" + book_ID + ",'" + title + "','" + author + "'," + price + ",'" + description + "'," + category_ID + ")",
        //
        (err, result) => {
            if (err) {
                console.error("Error while retrieve the data" + err);
            } else {
                res.send({insert: "success"});
            }
        })
})
//update 仅仅更新book表中的 描述 项
router.put("/", (req, res) => {
    var book_ID = req.body.book_ID;
    var title = req.body.title;
    var author = req.body.author;
    var price = req.body.price;
    var description = req.body.description;

    var category_ID = req.body.category_ID;
    //可以更新除了bookID外的所以列
    let sql = ` UPDATE book SET title='${title}',author='${author}',price=${price},description='${description}'  where book_ID=${book_ID} `;

    console.log(sql)
    connection.query(sql,
        (err, result) => {
            if (err) {
                console.error("Error while Updating the data" + err);
            } else {
                res.send({update: "success"});
            }
        })
//------------------------------------------------------------------------
})

router.delete("/:id", (req, res) => {
    connection.query("delete from book where book_ID=" + req.params.id, (err, records, fields) => {
        if (err) {
            console.error("Error while deleting the data");
        } else {
            res.send({delete: "Delete Sucess"});
        }
    })
})

module.exports = router;
